<!DOCTYPE html>
<html lang="zh">
<head>
    <meta charset="UTF-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="baidu-site-verification" content="SCzNOI2Wq6" />
    <meta name="renderer" content="webkit">
    <meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1, user-scalable=no">
    <title>
        daozhuang
    </title>
    
<link rel="stylesheet" href="/libs/highlight/styles/monokai-sublime.css">

    
<link rel="stylesheet" href="/libs/font-awesome/css/font-awesome.min.css">

    
<link rel="stylesheet" href="/css/style.css">

<meta name="generator" content="Hexo 4.2.1"></head>

<body id="bodyx">
    <div class="hd posts">
    <a href="/index.html"><i class="fa fa-reply replay-btn" aria-hidden="true"></i></a>
    <div class="post-title">
        <p>
            数据库复杂的 SQL 语句总结
        </p>
        <hr>
    </div>
    <div class="post-content">
        <h1 id="MySQL数据库"><a href="#MySQL数据库" class="headerlink" title="MySQL数据库"></a>MySQL数据库</h1><h2 id="sql语句中写-if-判断语句"><a href="#sql语句中写-if-判断语句" class="headerlink" title="sql语句中写 if 判断语句"></a>sql语句中写 if 判断语句</h2><p>– 判断函数  判断表达式可以是子查询<br>– if(exp, v1, v2) 根据 exp 判断条件, true则返回 v1，false 则返回 v2。<br>– 判断条件中还可以加  or  and  关键字</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> </span><br><span class="line"><span class="keyword">IF</span>( <span class="number">1</span> &gt; <span class="number">2</span>, <span class="literal">true</span>, <span class="literal">false</span>) <span class="keyword">as</span> <span class="string">`is_biding`</span>,</span><br><span class="line"><span class="keyword">IF</span>((<span class="keyword">select</span> <span class="keyword">count</span>(*) <span class="keyword">from</span> edu_user <span class="keyword">where</span> user_id = <span class="string">'0'</span>) &gt; <span class="number">0</span> <span class="keyword">OR</span> s.id = <span class="string">'0'</span>, s.video_url,<span class="literal">null</span> ) <span class="keyword">as</span> <span class="string">`s.video_url`</span></span><br><span class="line"><span class="keyword">from</span> edu_course s <span class="keyword">where</span> s.id = <span class="number">0</span></span><br></pre></td></tr></table></figure>
<h2 id="SQL中的case-when-then-else-end用法"><a href="#SQL中的case-when-then-else-end用法" class="headerlink" title="SQL中的case when then else end用法"></a>SQL中的case when then else end用法</h2><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> country,</span><br><span class="line">    <span class="keyword">SUM</span>(<span class="keyword">CASE</span> <span class="keyword">WHEN</span> p.sex = <span class="string">'1'</span> <span class="keyword">THEN</span> p.population <span class="keyword">ELSE</span> <span class="number">0</span> <span class="keyword">END</span>) <span class="keyword">AS</span> <span class="string">'男'</span>,</span><br><span class="line">    <span class="keyword">SUM</span>(<span class="keyword">CASE</span> <span class="keyword">WHEN</span> p.sex = <span class="string">'2'</span> <span class="keyword">THEN</span> p.population <span class="keyword">ELSE</span> <span class="number">0</span> <span class="keyword">END</span>) <span class="keyword">AS</span> <span class="string">'女'</span></span><br><span class="line"><span class="keyword">FROM</span></span><br><span class="line">    t_b_population p</span><br><span class="line"><span class="keyword">GROUP</span> <span class="keyword">BY</span> country;</span><br></pre></td></tr></table></figure>
<h2 id="sql-查询重复数据"><a href="#sql-查询重复数据" class="headerlink" title="sql 查询重复数据"></a>sql 查询重复数据</h2><h6 id="查询-Person-表中重复的-Email"><a href="#查询-Person-表中重复的-Email" class="headerlink" title="查询 Person 表中重复的 Email"></a>查询 Person 表中重复的 Email</h6><pre><code>+----+---------+
| Id | Email   |
+----+---------+
| 1  | a@b.com |
| 2  | c@d.com |
| 3  | a@b.com |
+----+---------+</code></pre><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 方法一：</span></span><br><span class="line"><span class="keyword">select</span> Email <span class="keyword">from</span> (<span class="keyword">select</span> <span class="keyword">count</span>(<span class="number">1</span>) <span class="keyword">as</span> t,Email <span class="keyword">from</span> Person <span class="keyword">group</span> <span class="keyword">by</span> Email)r  <span class="keyword">where</span> r.t&gt;<span class="number">1</span>;</span><br><span class="line"><span class="comment">-- 方法二：</span></span><br><span class="line"><span class="keyword">select</span> Email <span class="keyword">from</span> Person <span class="keyword">group</span> <span class="keyword">by</span> Email <span class="keyword">having</span> <span class="keyword">count</span>(Email) &gt; <span class="number">1</span>;</span><br></pre></td></tr></table></figure>


    </div>

    
        <hr class="fhr">
        <div id="vcomments"></div>
    
</div>
    <div class="footer" id="footer">
    <p>Copyright © 2020 <a class="flink" href="https://hexo.io" target="_blank" rel="noopener">Hexo</a>-<a class="flink" href="https://github.com/sanjinhub/hexo-theme-geek" target="_blank" rel="noopener">Geek</a>.
        <label class="el-switch el-switch-green el-switch-sm" style="vertical-align: sub;">
            <input type="checkbox" name="switch" id="update_style">
            <span class="el-switch-style"></span>
        </label>
<!--         <script type="text/javascript">
        var cnzz_protocol = (("https:" == document.location.protocol) ? "https://" : "http://");
        document.write(unescape("%3Cspan id='cnzz_stat_icon_1278548644'%3E%3C/span%3E%3Cscript src='" + cnzz_protocol + "v1.cnzz.com/stat.php%3Fid%3D1278548644%26show%3Dpic1' type='text/javascript'%3E%3C/script%3E"));
        </script> -->
    </p>
</div>
<input type="hidden" id="web_style" value="black">
<input type="hidden" id="valine_appid" value="c994h5wXLa8fSMa424BTyVhs-gzGzoHsz">
<input type="hidden" id="valine_appKey" value="4tFbwCi4sYo3yRFgiNFpQd4N">

<script src="/libs/jquery.min.js"></script>


<script src="/libs/highlight/highlight.pack.js"></script>

<script src='//cdn.jsdelivr.net/npm/valine@1.3.10/dist/Valine.min.js'></script>

<script src="/js/js.js"></script>

<style type="text/css">
.v * {
    color: #698fca;
}

.v .vlist .vcard .vhead .vsys {
    color: #3a3e4a;
}

.v .vlist .vcard .vh .vmeta .vat {
    color: #638fd5;
}

.v .vlist .vcard .vhead .vnick {
    color: #6ba1ff;
}

.v a {
    color: #8696b1;
}

.v .vlist .vcard .vhead .vnick:hover {
    color: #669bfc;
}
</style>
</body>

</html>